/*
 * Created on 14 �.�. 2547
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.softplus.carrefour.hd.bltier.admin;

import com.softplus.carrefour.hd.bltier.entity.CfhdCaseHead;
import com.softplus.carrefour.hd.bltier.entity.Country;
import com.softplus.carrefour.hd.bltier.entity.UserAccount;
import com.softplus.carrefour.hd.bltier.entity.UserOfficeHour;
import com.softplus.carrefour.hd.bltier.entity.UserOfficeHourPK;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.Vector;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Session;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.epro.standard.frontend.lov.LOVKeyword;

import com.softplus.carrefour.CFException;
import com.softplus.carrefour.InfrastructureException;
import com.softplus.carrefour.hd.bltier.db.HibernateUtil;
import com.softplus.carrefour.hd.bltier.entity.CfhdCaseDetail;
import com.softplus.carrefour.hd.bltier.entity.UserLevel;
import com.softplus.carrefour.hd.bltier.util.LOVtemplate;


/**
 * @author Kowit
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class UserLevelManage implements IUserLevelControl, IUserLevelViewer {

    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelViewer#viewAllUser()
     */
    private static final Log log = LogFactory.getLog(UserLevelManage.class);


    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelViewer#addUserLevel()
     */

    public void addUserLevel(UserLevel user) throws CFException {

        HibernateUtil.insertEntity(user);


        //        UserOfficeHourManage uom = new UserOfficeHourManage();
        //        UserOfficeHour u = uom.getUserOfficeHourByPK(new UserOfficeHourPK(user.getUlevel(),UserAccount.getUserAccount(user.getUserId()).getCountry().getCountryId()));       
        //        if(u == null)
        //        {
        //          UserOfficeHour uoh = new UserOfficeHour();
        //          uoh.setComp_id(new UserOfficeHourPK(user.getUlevel(),UserAccount.getUserAccount(user.getUserId()).getCountry().getCountryId()));
        //          uoh.setSun(true);
        //          uoh.setMon(true);
        //          uoh.setTue(true);
        //          uoh.setWed(true);
        //          uoh.setThu(true);
        //          uoh.setFri(true);
        //          uoh.setSat(true);
        //          uoh.setOprHourStart("0000");
        //          uoh.setOprHourEnd("2359");
        //          uoh.setCredBy(user.getCredBy());
        //          uoh.setCredDate(user.getCredDate());
        //          uoh.setUpdBy(user.getUpdBy());
        //          uoh.setUpdDate(user.getUpdDate());
        //          uom.addOfficeHour(uoh);
        //        }
    }

    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelControl#deleteUserLevel(com.softplus.carrefour.hd.bltier.entity.UserLevel)
     */

    public void deleteUserLevel(UserLevel user) throws CFException {
        if (this.IsHasCase(user.getUserId()))
            throw new CFException(new Exception(user.getUserId() + 
                                                " has case(s)"));
        else
            HibernateUtil.deleteEntity(user);
    }

    public Vector getAllUser() throws CFException {
        String hql = null;
        hql = "from UserLevel u ";

        List ls = null;
        try {
            ls = HibernateUtil.getSession().createQuery(hql).list();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);
    }


    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelViewer#getUserByLevel()
     */

    public Vector getUserByLevel(String level) throws CFException {
        String hql = null;
        if (level != null && !level.equals(""))
            hql = "from UserLevel u where u.ulevel = '" + level + "'";
        else
            throw new CFException("null user level");

        List ls = null;
        try {
            ls = HibernateUtil.getSession().createQuery(hql).list();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);

    }


    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelViewer#getLOVuserID()
     */

    public Vector getLOVuserID(LOVKeyword key) throws CFException {

        String searchValue = key.getMessage();
        Vector checkBoxs = key.getCheckBox();
        HashMap conds = key.getCondition();

        if (checkBoxs.size() == 0) { //no checkbox was checked 
            checkBoxs.addElement("0");
        }

        HashMap serachCols = new HashMap();

        serachCols.put("0", " UA.USER_ID ");
        serachCols.put("1", " UA.USER_NAME ");

        String notInUser = "";
        String userIDs = "";

        String inCountryCondition = "";
        String countryID = "";

        String inresourceCondition = 
            " and exists (select resource_id from RESOURCES r where r.user_id = UA.USER_ID ) and UA.USER_ID not in (select user_id from user_level) ";

        if (conds != null) {
            countryID = (String)conds.get("countryID");
            userIDs = (String)conds.get("userIDs");
        }

        if ((userIDs != null) && !(userIDs.compareTo("") == 0)) {
            notInUser = " and UA.USER_ID not in ( " + userIDs + ")";
        }

        if ((countryID != null) && !(countryID.compareTo("") == 0)) {
            inCountryCondition = " and (UA.COUNTRY_ID = '" + countryID + "')";
        }


        String orderBy = " order by UA.USER_ID ";
        String sql = 
            "select " + "UA.USER_NAME as UA_USER_NAME," + "UA.USER_SURNAME as UA_USER_SURNAME," + 
            "UA.ROLE_ID as UA_ROLE_ID," + "UA.COUNTRY_ID as UA_COUNTRY_ID," + 
            "UA.USER_ID as UA_USER_ID," + "R.ROLE_NAME as R_ROLE_NAME," + 
            "C.COUNTRY_NAME as C_COUNTRY_NAME" + 
            " from USER_ACCOUNT UA ,ROLE R,COUNTRY C" + 
            " where UA.ACTIVE = 'Y' " + " and (UA.ROLE_ID = R.ROLE_ID) " + 
            " and (UA.COUNTRY_ID = C.COUNTRY_ID) " + " and (";


        return new LOVtemplate().getLOV(sql, 
                                        new String[] { "UA_USER_ID", "UA_USER_NAME", 
                                                       "UA_USER_SURNAME", 
                                                       "R_ROLE_NAME", 
                                                       "C_COUNTRY_NAME", 
                                                       "UA_COUNTRY_ID" }, 
                                        searchValue, serachCols, checkBoxs, "", 
                                        notInUser + inCountryCondition + 
                                        inresourceCondition);
    }


    /* (non-Javadoc)
     * @see com.softplus.carrefour.hd.bltier.admin.IUserLevelViewer#getUserForCountry(java.lang.String)
     */

    public Vector getUserForCountry(String level) throws CFException {
        String hql = null;
        if (level != null && !level.equals(""))
            hql = "from UserLevel u where u.ulevel = '" + level + "'";
        else
            throw new CFException("null user level");

        List ls = null;
        try {
            ls = HibernateUtil.getSession().createQuery(hql).list();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);

    }

    public String getUserLevelByUserID(String userID) throws CFException {
        UserLevel ul = null;
        try {
            ul = 
 (UserLevel)HibernateUtil.getSession().get(UserLevel.class, userID);
        } catch (HibernateException e) {
            e.printStackTrace();
        } catch (InfrastructureException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        if (ul == null)
            return null;
        return ul.getUlevel();
    }

    public UserLevel getUserLevel(String userID) throws CFException {
        UserLevel ul = null;
        try {
            ul = 
 (UserLevel)HibernateUtil.getSession().get(UserLevel.class, userID);
        } catch (HibernateException e) {
            e.printStackTrace();
        } catch (InfrastructureException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return ul;
    }

    public UserAccount getUserAccount(String userID) throws CFException {
        UserAccount ua = null;
        try {
            ua = 
 (UserAccount)HibernateUtil.getSession().get(UserAccount.class, userID);
        } catch (HibernateException e) {
            e.printStackTrace();
        } catch (InfrastructureException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return ua;
    }

    public Vector getUserLevelForAddOfficeHour(String countryID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Set allUserLevel = new HashSet();
        UserOfficeHourManage um = new UserOfficeHourManage();

        allUserLevel.add(UserLevel.LEVEL_1_COUNTRY_HELPDESK);
        allUserLevel.add(UserLevel.LEVEL_2_COUNTRY_APPLICATION_TEAM_BACK_OFFICE);
        allUserLevel.add(UserLevel.ADMIN);
        allUserLevel.add(UserLevel.ADMIN_COUNTRY);

        String majorCountry = um.getMajorCountry();

        if (majorCountry != null) {
            if (um.IsHasMajorLevel(countryID) || 
                countryID.equals(majorCountry)) {
                allUserLevel.add(UserLevel.LEVEL_2_AMS_ZONE_ASIA);
                allUserLevel.add(UserLevel.LEVEL_3_AMS_BANGKOK);
                allUserLevel.add(UserLevel.PRODUCTION_STAFF);
            }
        } else {

            allUserLevel.add(UserLevel.LEVEL_2_AMS_ZONE_ASIA);
            allUserLevel.add(UserLevel.LEVEL_3_AMS_BANGKOK);
            allUserLevel.add(UserLevel.PRODUCTION_STAFF);

        }
        try {
            String sql = 
                "select ulevel UL from user_office_hour where country_id = '" + 
                countryID + "'";

            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                allUserLevel.remove(rs.getString("UL"));
            }
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        Vector sortedLevel = new Vector(new TreeSet(allUserLevel));
        return sortedLevel;

    }

    public Vector getUserActionByUserID(String caseType, 
                                        String userID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector acs = new Vector();

        try {
            String sql = 
                "SELECT   UA.ANS C1, UA.MNE C2, UA.REJ C3, UA.TFR C4, UA.REQ C5, UA.CHS C6, UA.APV C7 FROM CFHD_USER_ACTION UA,USER_LEVEL UL WHERE (UA.ULEVEL = UL.ULEVEL) AND UL.USER_ID ='" + 
                userID + "'  and UA.CASE_TYPE = '" + caseType + "'";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            String a1 = "";
            while (rs.next()) {
                a1 = rs.getString("C1");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_ANSWER);

                a1 = rs.getString("C2");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_MANUAL_ESCALATE);

                a1 = rs.getString("C3");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_REJECT);

                a1 = rs.getString("C4");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_TRANSFER);

                a1 = rs.getString("C5");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_REQUALIFY);

                a1 = rs.getString("C6");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_CHANGE_SERVERITY);

                a1 = rs.getString("C7");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_APPROVE);

            }
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }

        return acs;
    }

    public Vector getUserActionByUserLevel(String caseType, 
                                           String userLevel) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector acs = new Vector();

        try {
            String sql = 
                "SELECT   UA.ANS C1, UA.MNE C2, UA.REJ C3, UA.TFR C4, UA.REQ C5, UA.CHS C6, UA.APV C7 FROM CFHD_USER_ACTION UA WHERE  UA.ULEVEL ='" + 
                userLevel + "'  and UA.CASE_TYPE = '" + caseType + "'";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            String a1 = "";
            while (rs.next()) {
                a1 = rs.getString("C1");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_ANSWER);

                a1 = rs.getString("C2");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_MANUAL_ESCALATE);

                a1 = rs.getString("C3");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_REJECT);

                a1 = rs.getString("C4");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_TRANSFER);

                a1 = rs.getString("C5");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_REQUALIFY);

                a1 = rs.getString("C6");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_CHANGE_SERVERITY);

                a1 = rs.getString("C7");
                if ("Y".equals(a1))
                    acs.add(CfhdCaseDetail.ACTION_APPROVE);

            }
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }

        return acs;
    }

    public Vector getTransferUser(String userID) throws CFException {

        List ls = null;
        try {
            Session session = HibernateUtil.getSession();

            UserLevel u1 = (UserLevel)session.get(UserLevel.class, userID);
            if (u1 == null)
                throw new InfrastructureException(new Exception("Could not get userLevel"));

            String ul = u1.getUlevel();
            System.out.println("ul " + ul);
            String hql = null;
            if (ul.equals(UserLevel.LEVEL_1_COUNTRY_HELPDESK) || 
                ul.equals(UserLevel.LEVEL_2_COUNTRY_APPLICATION_TEAM_BACK_OFFICE))
                hql = 
"from UserLevel ul where ul.userAccount.country.countryId = '" + 
  u1.getUserAccount().getCountry().getCountryId() + "' and ul.ulevel = '" + 
  ul + "' and ul.userId !='" + userID + "'";
            else
                hql = 
"from UserLevel ul where  ul.ulevel = '" + ul + "' and ul.userId !='" + 
  userID + "'";

            ls = session.createQuery(hql).list();
        } catch (HibernateException e) {
            throw new InfrastructureException(e);
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);

    }

    public boolean IsHasCase(String userID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;
        try {
            String sql = 
                "select USER_ID from cfhd_case_head where user_id = '" + 
                userID + "' and case_status = '" + 
                CfhdCaseHead.STATUS_VALIDATED + "'";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                if (rs.getString("USER_ID") != null)
                    result = true;
            }

            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw new InfrastructureException(e);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }

        return result;

    }

    public boolean IsTimezoneExistForUser(String userID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;
        try {
            String sql = 
                "SELECT tz.country_id FROM TIMEZONE tz, country cn, user_account ua WHERE ua.country_id = cn.country_id AND tz.country_id = cn.country_id AND ua.user_id = '" + 
                userID + "'";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next())
                result = true;

            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }

        return result;

    }

}
